package com.tust.dao;

import com.tust.bean.StudentCourse;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


// 学生选课管理和成绩管理api
public class StudentCourseDao {

    // 声明一个连接对象引用 用来接收与数据库连接的connection实例对象
    private Connection connection;

    // 声明一个PreparedStatement对象引用
    private PreparedStatement statement;


    public StudentCourseDao(Connection connection) {
        // 获取到连接实例对象与数据库建立连接
        this.connection = connection;
    }

    /**
     * 添加选课信息
     * @param studentCourse
     * @return
     */
    public boolean insert(StudentCourse studentCourse) {

        try {

            // 检查是否已经存在此选课信息
            statement = connection.prepareStatement("SELECT id, is_delete FROM stu_course_relation WHERE student_id = ? AND tc_id = ?");
            statement.setInt(1,studentCourse.getSid());
            statement.setInt(2,studentCourse.getTcid());
            try (ResultSet resultSet = statement.executeQuery()) { // 自动关闭ResultSet接口

                // 如果存在选课记录
                if (resultSet.next()) {

                    // 如果不是历史记录，打印请勿重复添加选课！
                    if(resultSet.getInt(2) == 0) {
                        System.out.println("该选课记录已存在，请勿重复添加！");
                        return false;
                    } else {
                        // 否则将此选课记录显示出来
                        statement = connection.prepareStatement("UPDATE stu_course_relation SET is_delete = 0 WHERE id = ?");
                        statement.setInt(1,resultSet.getInt(1));
                    }

                } else {
                    // 不存在 直接新增
                    statement = connection.prepareStatement("INSERT INTO stu_course_relation (student_id, tc_id) VALUES (?, ?)");

                    statement.setInt(1, studentCourse.getSid());
                    statement.setInt(2, studentCourse.getTcid());
                }

                statement.executeUpdate();

                return true;

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }


        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;

    }

    /**
     * 删除所有学生的所有选课记录
     * @return
     */
    public boolean delete() {

        try {
            statement = connection.prepareStatement("UPDATE stu_course_relation SET is_delete = 1 WHERE is_delete = 0");
            statement.executeUpdate();

            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定学生的所有选课记录
     * @param sid
     * @return
     */
    public boolean delete(int sid) {

        try {
            statement = connection.prepareStatement("UPDATE stu_course_relation SET is_delete = 1 WHERE student_id = ? AND is_delete = 0");

            statement.setInt(1,sid);
            statement.executeUpdate();

            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定课程的所有选课记录
     * @param cid
     * @return
     */
    public boolean delete(String cid) {

        try {

            // 1.获取所有相关课程的授课id
            List<Integer> list = new TeachCourseDao(connection).query(cid);

            // 2.删除所有相关的选课记录

            // 构建带有占位符的预处理语句
            StringBuilder sql = new StringBuilder("UPDATE stu_course_relation SET is_delete = 1 WHERE tc_id IN (");
            for (int i = 0; i < list.size(); i++) {
                sql.append("?");
                if (i < list.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(") AND is_delete = 0");

            // 创建预处理语句
            PreparedStatement statement = connection.prepareStatement(sql.toString());

            // 设置预处理语句的参数值
            for (int i = 0; i < list.size(); i++) {
                statement.setInt(i + 1, list.get(i));
            }

            // 执行查询
            // statement = connection.prepareStatement("UPDATE stu_course_relation SET is_delete = 1 WHERE tc_id IN (" + list + ") AND is_delete = 0");
            statement.executeUpdate();
            return true;

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定id的选课记录
     * @param id
     * @return
     */
    public boolean deleteById(int id) {

        try {
            statement = connection.prepareStatement("UPDATE stu_course_relation SET is_delete = 1 WHERE id = ? AND is_delete = 0");
            statement.setInt(1,id);
            statement.executeUpdate();

            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 更新选课信息
     * @param studentCourse
     * @return
     */
    public boolean update(StudentCourse studentCourse) {


        try {
            statement = connection.prepareStatement("UPDATE stu_course_relation SET student_id = ?, tc_id = ? WHERE id = ? AND is_delete = 0");
            statement.setInt(1,studentCourse.getSid());
            statement.setInt(2,studentCourse.getTcid());
            statement.setInt(3,studentCourse.getScid());
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 查询所有指定学生的选课记录
     * @return
     */
    public List<StudentCourse> query(int sid) {
        List<StudentCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT sc.id, sc.student_id, s.name, sc.tc_id, tc.teacher_id, t.name, tc.course_id, c.name FROM stu_course_relation sc JOIN student s ON sc.student_id = s.id JOIN tea_course_relation tc ON sc.tc_id = tc.id JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE sc.student_id = ? AND sc.is_delete = 0");
            statement.setInt(1,sid);
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    StudentCourse studentCourse = new StudentCourse();
                    studentCourse.setScid(resultSet.getInt(1));
                    studentCourse.setSid(resultSet.getInt(2));
                    studentCourse.setSname(resultSet.getString(3));
                    studentCourse.setTcid(resultSet.getInt(4));
                    studentCourse.setTid(resultSet.getInt(5));
                    studentCourse.setTname(resultSet.getString(6));
                    studentCourse.setCid(resultSet.getString(7));
                    studentCourse.setCname(resultSet.getString(8));
                    list.add(studentCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }


    /**
     * 查询所有的选课记录
     * @return
     */
    public List<StudentCourse> query() {
        List<StudentCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT sc.id, sc.student_id, s.name, sc.tc_id, tc.teacher_id, t.name, tc.course_id, c.name FROM stu_course_relation sc JOIN student s ON sc.student_id = s.id JOIN tea_course_relation tc ON sc.tc_id = tc.id JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE sc.is_delete = 0");
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    StudentCourse studentCourse = new StudentCourse();
                    studentCourse.setScid(resultSet.getInt(1));
                    studentCourse.setSid(resultSet.getInt(2));
                    studentCourse.setSname(resultSet.getString(3));
                    studentCourse.setTcid(resultSet.getInt(4));
                    studentCourse.setTid(resultSet.getInt(5));
                    studentCourse.setTname(resultSet.getString(6));
                    studentCourse.setCid(resultSet.getString(7));
                    studentCourse.setCname(resultSet.getString(8));
                    list.add(studentCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 添加成绩
     * @param studentCourse
     * @return
     */
    public boolean insertScore(StudentCourse studentCourse) {

        try {

            statement = connection.prepareStatement("UPDATE stu_course_relation SET course_score = ? WHERE id = ? AND is_delete = 0");

            statement.setDouble(1, Double.parseDouble(studentCourse.getScore()));
            statement.setInt(2, studentCourse.getScid());

            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除指定成绩
     * @param studentCourse
     * @return
     */
    public boolean deleteScore(StudentCourse studentCourse) {

        try {

            statement = connection.prepareStatement("UPDATE stu_course_relation SET course_score = NULL WHERE id = ? AND is_delete = 0");

            statement.setInt(1, studentCourse.getScid());

            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 删除所有成绩
     * @param
     * @return
     */
    public boolean deleteScore() {

        try {

            statement = connection.prepareStatement("UPDATE stu_course_relation SET course_score = NULL WHERE is_delete = 0");

            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }

    /**
     * 更新课程成绩
     * @param studentCourse
     * @return
     */
    public boolean updateScore(StudentCourse studentCourse) {

        try {

            statement = connection.prepareStatement("UPDATE stu_course_relation SET course_score = ? WHERE id = ? AND is_delete = 0");

            statement.setInt(1,Integer.parseInt(studentCourse.getScore()));
            statement.setInt(2, studentCourse.getScid());
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return false;
    }


    /**
     * 查询所有的成绩记录
     * @return
     */
    public List<StudentCourse> queryScore() {
        List<StudentCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT sc.id, sc.student_id, sc.tc_id, tc.teacher_id, t.name, tc.course_id, c.name, sc.course_score FROM stu_course_relation sc JOIN tea_course_relation tc ON sc.tc_id = tc.id JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE sc.is_delete = 0");
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    StudentCourse studentCourse = new StudentCourse();
                    studentCourse.setScid(resultSet.getInt(1));
                    studentCourse.setSid(resultSet.getInt(2));
                    studentCourse.setTcid(resultSet.getInt(3));
                    studentCourse.setTid(resultSet.getInt(4));
                    studentCourse.setTname(resultSet.getString(5));
                    studentCourse.setCid(resultSet.getString(6));
                    studentCourse.setCname(resultSet.getString(7));

                    // 判断分数是否为null
                    double score = resultSet.getDouble(8);
                    if (resultSet.wasNull()) {
                        studentCourse.setScore("null");
                    } else {
                        studentCourse.setScore(String.valueOf(score));
                    }

                    list.add(studentCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 查询指定学生的课程成绩
     * @param sid
     * @return
     */
    public List<StudentCourse> queryScore(int sid) {
        List<StudentCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT sc.id, sc.student_id, sc.tc_id, tc.teacher_id, t.name, tc.course_id, c.name, sc.course_score FROM stu_course_relation sc JOIN tea_course_relation tc ON sc.tc_id = tc.id JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE sc.student_id = ? AND sc.is_delete = 0");
            statement.setInt(1,sid);
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    StudentCourse studentCourse = new StudentCourse();
                    studentCourse.setScid(resultSet.getInt(1));
                    studentCourse.setSid(resultSet.getInt(2));
                    studentCourse.setTcid(resultSet.getInt(3));
                    studentCourse.setTid(resultSet.getInt(4));
                    studentCourse.setTname(resultSet.getString(5));
                    studentCourse.setCid(resultSet.getString(6));
                    studentCourse.setCname(resultSet.getString(7));

                    // 判断分数是否为null
                    double score = resultSet.getDouble(8);
                    if (resultSet.wasNull()) {
                        studentCourse.setScore("null");
                    } else {
                        studentCourse.setScore(String.valueOf(score));
                    }

                    list.add(studentCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 查询指定课程的所有成绩
     * @param cid
     * @return
     */
    public List<StudentCourse> queryScore(String cid) {
        List<StudentCourse> list=new ArrayList<>();
        try {
            statement = connection.prepareStatement("SELECT sc.id, sc.student_id, sc.tc_id, tc.teacher_id, t.name, tc.course_id, c.name, sc.course_score FROM stu_course_relation sc JOIN tea_course_relation tc ON sc.tc_id = tc.id JOIN teacher t ON tc.teacher_id = t.id JOIN course c ON tc.course_id = c.id WHERE c.id = ? AND sc.is_delete = 0");
            statement.setString(1,cid);
            try (ResultSet resultSet= statement.executeQuery()) {

                while(resultSet.next()){
                    StudentCourse studentCourse = new StudentCourse();
                    studentCourse.setScid(resultSet.getInt(1));
                    studentCourse.setSid(resultSet.getInt(2));
                    studentCourse.setTcid(resultSet.getInt(3));
                    studentCourse.setTid(resultSet.getInt(4));
                    studentCourse.setTname(resultSet.getString(5));
                    studentCourse.setCid(resultSet.getString(6));
                    studentCourse.setCname(resultSet.getString(7));

                    // 判断分数是否为null
                    double score = resultSet.getDouble(8);
                    if (resultSet.wasNull()) {
                        studentCourse.setScore("null");
                    } else {
                        studentCourse.setScore(String.valueOf(score));
                    }

                    list.add(studentCourse);
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

    /**
     * 关闭数据库连接中的statement 对象 释放相关资源
     */
    public void close() {

        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
